***********************************************************************
5/13/09:  FIRST VERSION CREATED.  BUILDS OFF OF MAIN CONS DATA CREATED
	  IN ~/JIM/POVERTY/POVERTY_TRENDS_HEALTH.SAS

6/3/10:  ADDED CODE TO OUTPUT DATA TO STATA SO WE CAN RUN MELLY 
	 DECOMPOSITION, NOTE ADDED AN ENDSAS SO ONLY RUN PROGRAM 
	 TO THIS POINT

11/3/10:  ADDED 2009 DATA, NOTE MELLY*.DAT STILL ONLY THROUGH 2008 B/C
	  INCOME DATA ONLY GO THROUGH 08 AT THIS POINT

2/17/12:  ADDED 2010 DATA

3/22/12:  ADDED PCTILES BY DEMOG GROUP

12/21/12:  ADDED 2011 DATA

2/2/14:  ADDED 2012 DATA, NOTE ONLY GO THROUGH OUTPUTING MELLY DATA, NO 
		LONGER CALCULATE PREDICTED CONSUMPTION
		
3/31/14:  SWITCHED MAIN OUTPUT VARIABLES AND VARS FOR CONSUMPTION QUINTILES
	  TO CONS5 AND CONS10

7/29/14:  DELETED OBS FROM 72-73 THAT DID NOT REPORT A RENTEQ, AND ADJUSTED HFLOW 
				  TO ONLY INCLUDE RENTEQ (SEARCH "DELETE")

12/9/15:  EXTENDED TO INCLUDE DATA THUR SY 2014

11/21/17:  EXTENDED TO INCLUDE DATA THUR SY 2015 & 2016

10/31/18:  EXTENDED TO INCLUDE DATA THUR SY 2017
		
***********************************************************************;
OPTIONS NOLABEL;

FILENAME outfile1 '~/jim/inequality/output/pctiles.dat';
FILENAME outfile3 '~/jim/inequality/output/demog.dat';
FILENAME outfile4 '~/jim/inequality/output/stata_ce_melly.dat';
FILENAME outfile5 '~/jim/inequality/output/pctiles_by_demog.dat';
FILENAME outfile6 '~/jim/inequality/output/cons_by_ass_quint.dat';
FILENAME outfile7 '~/jim/inequality/output/means_core_total.dat';

LIBNAME alldat '~/jim/data';

DATA poverty;
  SET alldat.cons_final60_17(KEEP=qyear cons1-cons9 ref_year newid wgt20 educ_ref ftype ref_race region 
		     bls_urbn incweek1 incweek2 earn_comp hispanic age_ref ed_type sex_ref sex scale
		     fdhomepq2 fdhomecq2 rendwepq rendwecq utilpq utilcq hflow tflow2 tflow
		     d_fdhome d_rent d_utility fdhome totrentdwe Utilites
		     ckbkactx_ ckbkactx secestx_ secestx usbndx_ usbndx savacctx_ savacctx assets72 
				 tot_debt2 rt1 srv_year respstat cutenure
				 d_gas gasoline_oil
				 d_othvehic d_autofin d_pubtrans automobileops Publictransother
				 gasmopq gasmocq 
				 transpq transcq
				 n_tot_vflow1 vehfinpq vehfincq vehicpq vehiccq cartknpq cartkncq 
				 cartkupq cartkucq caropq carocq renteq
				 LIQUIDX STOCKX LIQUIDX_ STOCKX_
				 perslt18 fam_size fincbtax occ_recode topcode_inc earncomp d_owndwell houseval 				 married educ_code totexp2 totowndwe state1 totexp fam_type marital1   
        			 rooms publhous govtcost stove microw frig freezer disposal dishw
        			 win_ac cen_ac washer dryer bedroom bathrm hlfbath smsastat intvmo
				 owndwepq owndwecq totexp locat vehq fincatax nonincmx fica
				 desflg interi houspq houscq exclude
				 );


RUN;

PROC SORT DATA=alldat.famstep8081;
   BY newid;
RUN;
PROC SORT DATA=alldat.mortgage8817;
   BY newid;
RUN;
PROC SORT DATA=alldat.home_eq9117;
   BY newid;
RUN;
PROC SORT DATA=alldat.heloc9117;
   BY newid;
RUN;
PROC SORT DATA=alldat.home_val8417;
   BY newid;
RUN;
PROC SORT DATA=poverty;
   BY newid;
RUN;

DATA poverty;
   MERGE poverty(IN=in1)                     
   			 alldat.famstep8081(KEEP=newid totpval)
   			 alldat.mortgage8817(KEEP=newid principal)
   			 alldat.home_eq9117(KEEP=newid prin_hel)
   			 alldat.heloc9117(KEEP=newid prin_loc)
   			 alldat.home_val8417(KEEP=newid n_hous totpval);
   BY newid;
   IF in1;
   
   ARRAY recode principal prin_hel prin_loc totpval houseval houseval2 n_hous;
   DO OVER recode;
      IF recode=. THEN recode=0;
   END;   
 
      * NOTE FOR 60-81 THIS IS JUST THE VALUE OF THE SAMPLE UNIT, NOT ADDITIONAL HOMES;
   IF 60 LE srv_year LE 62 THEN all_prop_val=houseval2;
      ELSE IF 70 LE srv_year LE 74 THEN all_prop_val=houseval;
      ELSE IF srv_year GE 80 THEN all_prop_val=totpval;
 

* DELETE OBSERVATIONS IN YEARS NOT REPORTED THUS FAR;
IF ref_year NE 1979;
IF ref_year NE 2018;


IF ref_year=1960 THEN cpi_u_rs_adj_17=4.383;
ELSE IF ref_year=1961 THEN cpi_u_rs_adj_17=4.446;
ELSE IF ref_year=1962 THEN cpi_u_rs_adj_17=4.45;
ELSE IF ref_year=1963 THEN cpi_u_rs_adj_17=4.439;
ELSE IF ref_year=1964 THEN cpi_u_rs_adj_17=4.427;
ELSE IF ref_year=1965 THEN cpi_u_rs_adj_17=4.407;
ELSE IF ref_year=1966 THEN cpi_u_rs_adj_17=4.333;
ELSE IF ref_year=1967 THEN cpi_u_rs_adj_17=4.246;
ELSE IF ref_year=1968 THEN cpi_u_rs_adj_17=4.131;
ELSE IF ref_year=1969 THEN cpi_u_rs_adj_17=3.997;
ELSE IF ref_year=1970 THEN cpi_u_rs_adj_17=3.849;
ELSE IF ref_year=1971 THEN cpi_u_rs_adj_17=3.726;
ELSE IF ref_year=1972 THEN cpi_u_rs_adj_17=3.656;
ELSE IF ref_year=1973 THEN cpi_u_rs_adj_17=3.477;
ELSE IF ref_year=1974 THEN cpi_u_rs_adj_17=3.193;
ELSE IF ref_year=1975 THEN cpi_u_rs_adj_17=2.982;
ELSE IF ref_year=1976 THEN cpi_u_rs_adj_17=2.848;
ELSE IF ref_year=1977 THEN cpi_u_rs_adj_17=2.708;
ELSE IF ref_year=1978 THEN cpi_u_rs_adj_17=2.553;
ELSE IF ref_year=1979 THEN cpi_u_rs_adj_17=2.349;
ELSE IF ref_year=1980 THEN cpi_u_rs_adj_17=2.128;
ELSE IF ref_year=1981 THEN cpi_u_rs_adj_17=1.958;
ELSE IF ref_year=1982 THEN cpi_u_rs_adj_17=1.861;
ELSE IF ref_year=1983 THEN cpi_u_rs_adj_17=1.799;
ELSE IF ref_year=1984 THEN cpi_u_rs_adj_17=1.74;
ELSE IF ref_year=1985 THEN cpi_u_rs_adj_17=1.695;
ELSE IF ref_year=1986 THEN cpi_u_rs_adj_17=1.679;
ELSE IF ref_year=1987 THEN cpi_u_rs_adj_17=1.636;
ELSE IF ref_year=1988 THEN cpi_u_rs_adj_17=1.591;
ELSE IF ref_year=1989 THEN cpi_u_rs_adj_17=1.537;
ELSE IF ref_year=1990 THEN cpi_u_rs_adj_17=1.476;
ELSE IF ref_year=1991 THEN cpi_u_rs_adj_17=1.435;
ELSE IF ref_year=1992 THEN cpi_u_rs_adj_17=1.411;
ELSE IF ref_year=1993 THEN cpi_u_rs_adj_17=1.387;
ELSE IF ref_year=1994 THEN cpi_u_rs_adj_17=1.369;
ELSE IF ref_year=1995 THEN cpi_u_rs_adj_17=1.348;
ELSE IF ref_year=1996 THEN cpi_u_rs_adj_17=1.323;
ELSE IF ref_year=1997 THEN cpi_u_rs_adj_17=1.305;
ELSE IF ref_year=1998 THEN cpi_u_rs_adj_17=1.298;
ELSE IF ref_year=1999 THEN cpi_u_rs_adj_17=1.281;
ELSE IF ref_year=2000 THEN cpi_u_rs_adj_17=1.249;
ELSE IF ref_year=2001 THEN cpi_u_rs_adj_17=1.224;
ELSE IF ref_year=2002 THEN cpi_u_rs_adj_17=1.214;
ELSE IF ref_year=2003 THEN cpi_u_rs_adj_17=1.197;
ELSE IF ref_year=2004 THEN cpi_u_rs_adj_17=1.174;
ELSE IF ref_year=2005 THEN cpi_u_rs_adj_17=1.145;
ELSE IF ref_year=2006 THEN cpi_u_rs_adj_17=1.117;
ELSE IF ref_year=2007 THEN cpi_u_rs_adj_17=1.095;
ELSE IF ref_year=2008 THEN cpi_u_rs_adj_17=1.063;
ELSE IF ref_year=2009 THEN cpi_u_rs_adj_17=1.075;
ELSE IF ref_year=2010 THEN cpi_u_rs_adj_17=1.066;
ELSE IF ref_year=2011 THEN cpi_u_rs_adj_17=1.042;
ELSE IF ref_year=2012 THEN cpi_u_rs_adj_17=1.028;
ELSE IF ref_year=2013 THEN cpi_u_rs_adj_17=1.021;
ELSE IF ref_year=2014 THEN cpi_u_rs_adj_17=1.012;
ELSE IF ref_year=2015 THEN cpi_u_rs_adj_17=1.018;
ELSE IF ref_year=2016 THEN cpi_u_rs_adj_17=1.013;
ELSE IF ref_year=2017 THEN cpi_u_rs_adj_17=1;
ELSE IF ref_year=2018 THEN cpi_u_rs_adj_17=0.98; /*NOTE: this is a place holder*/



IF 60 LE srv_year LE 63 THEN tot_trans=(publictransother+automobileops+n_tot_vflow1)/scale/4;
   ELSE IF 71 LE srv_year LE 74 THEN tot_trans=(d_gas+d_othvehic-d_autofin
   																						 +d_pubtrans+n_tot_vflow1)/scale/4; 
   ELSE IF srv_year GE 79 THEN tot_trans=(transpq+transcq-SUM(OF vehfinpq vehfincq vehicpq vehiccq 
		  																													cartknpq cartkncq cartkupq cartkucq 
		  																													caropq carocq)+n_tot_vflow1)/scale;

* DROP OBSERVATIONS IN 72-73 WITHOUT REPORTED RENTAL EQUIVALENT;
IF renteq=. AND cutenure LE 3 AND ref_year IN(1972, 1973) THEN DELETE;

* CONVERT CONS MEASURES TO INCLUDE ONLY RENTEQ FOR HOMEOWNERS. NOTE: cons7 is non-housing and cons 8 and 9 are income; 

   ARRAY contmp(1:6) cons1-cons6;
   DO t=1 TO 6; 
    IF cutenure LE 3 AND ref_year IN(1972, 1973) THEN contmp(t) = ((contmp(t)*scale)+(renteq*12/4)-(hflow/4))/scale ;
   END;
	 DROP t;
	 
* SUBTRACT TOTAL TRANSPORTATION AND ADD BACK VEH FLOW AND GAS AND MOTOR OIL;

IF 60 LE srv_year LE 63 THEN cons5=cons5-tot_trans+(gasoline_oil+n_tot_vflow1)/scale/4;
   ELSE IF 71 LE srv_year LE 74 THEN cons5=cons5-tot_trans
   																						 +(d_gas+n_tot_vflow1)/scale/4; 
   ELSE IF srv_year GE 79 THEN cons5=cons5-tot_trans+(gasmopq+gasmocq+n_tot_vflow1)/scale;

IF 60 LE srv_year LE 63 THEN cons10=cons5-(fdhome/scale/4);
   ELSE IF 71 LE srv_year LE 74 THEN cons10=cons5-(d_fdhome/scale/4);
   ELSE IF srv_year GE 79 THEN cons10=cons5-(SUM(OF fdhomepq2 fdhomecq2)/scale);

* DEFINE THE COMPLEMENT TO WELL-MEASURED;
cons11=cons3-cons5;
cons12=cons6-cons5;

   ARRAY contemp(1:12) cons1-cons12;
   ARRAY r_cons(1:12) ;

   DO t=1 TO 12; 
    r_cons(t)=contemp(t)*cpi_u_rs_adj_17;
   END;

r_cons5_2=r_cons5*r_cons5;
r_cons5_3=r_cons5*r_cons5*r_cons5;


*****************************************************************************
DEMOGRAPHICS 
*****************************************************************************;

* ADJUST FOR FACT THAT GENDER IS CALLED "SEX" IN 60S AND 70S;
IF sex_ref=. THEN sex_ref=sex;

male=2-sex_ref;

ARRAY fam(1:5); 
ARRAY fam_male(1:5); 
     
   DO t=1 TO 5; 
     IF ftype=t THEN fam(t)=1; 
       ELSE fam(t)=0; 
     fam_male(t)=fam(t)*male;
   END; 
     
ARRAY reg(1:4);
   DO t=1 to 4;
        IF region=t THEN reg(t)=1;
        ELSE reg(t)=0;
* REGION IS MISSING FOR RURALS 1980-1995, SO DEFINE REGION ONLY FOR URBANS;
        IF bls_urbn=2 THEN reg(t)=0;
end;

urban=2-bls_urbn;
      
IF ref_year GE 1979 THEN DO;
   IF ref_race=1 AND hispanic=0 THEN race1=1;
     ELSE race1=0;
   IF ref_race=2 AND hispanic=0 THEN race2=1;
     ELSE race2=0; 
   race3=1-race1-race2;
END; 
IF 1959 LE ref_year LE 1973 THEN DO;
   IF ref_race=1 THEN race1=1;
     ELSE race1=0;
   IF ref_race=2 THEN race2=1;
     ELSE race2=0;   
   race3=1-race1-race2;
END;
      
IF ref_year GE 1972 THEN DO;
  IF incweek1 GT 0 THEN emp=1; 
   ELSE IF incweek1 NE . THEN emp=0; 
END; 
IF 1959 LE ref_year LE 1961 THEN DO; 
  IF 0 LE earn_comp LE 5 THEN emp=1; 
   ELSE emp=0;
  IF earn_comp IN(2,5,7,9) THEN emp2=1;
   ELSE emp2=0; 
END; 
 
IF 1972 LE ref_year LE 1973 THEN DO;
  IF incweek2 GT 0 THEN emp2=1;
    ELSE IF incweek2 NE . THEN emp2=0;  
END; 
IF ref_year GE 1979 THEN DO; 
  IF incweek2 GT 0 THEN emp2=1; 
    ELSE emp2=0;
END; 
   
IF emp=1 AND emp2=1 THEN emp_type=1;   
   ELSE IF emp=1 AND emp2=0 THEN emp_type=2;
   ELSE IF emp=0 AND emp2=1 THEN emp_type=3;
   ELSE IF emp=0 AND emp2=0 THEN emp_type=4;
   ELSE emp_type=0;
   
IF race1=1 THEN race_type=1; 
   ELSE IF race2=1 THEN race_type=2;
   ELSE IF race3=1 THEN race_type=3; 
     
*ed_type already defined for 6061;
IF 1972 LE ref_year LE 1973 THEN DO;
        IF educ_ref IN(1,2,6) THEN ed_type=1;
           ELSE IF educ_ref=3 THEN ed_type=2;
           ELSE IF educ_ref=4 THEN ed_type=3;  
           ELSE IF educ_ref=5 THEN ed_type=4;
END; 
IF 801 LE qyear LE 955 THEN DO;
        IF educ_ref=1 or educ_ref=2 or educ_ref=7 then ed_type=1;
           ELSE IF educ_ref=3 then ed_type=2;
           ELSE IF educ_ref=4 then ed_type=3;
           ELSE IF educ_ref=5 or educ_ref=6 then ed_type=4;
END; 
IF qyear GT 955 THEN DO; 
        IF educ_ref=0 or educ_ref=10 or educ_ref=11 then ed_type=1;
           ELSE IF educ_ref=12 then ed_type=2;
           ELSE IF 13 LE educ_ref LE 14 then ed_type=3;
           ELSE IF 15 LE educ_ref LE 17 THEN ed_type=4;
END; 
         
ARRAY ed(1:4); 
  DO t=1 TO 4; 
     IF ed_type=t THEN ed(t)=1; 
       ELSE ed(t)=0;
END; 
         

IF 0 LE age_ref LE 34 THEN age_grp=1;
   ELSE IF 35 LE age_ref LE 49 THEN age_grp=2;
   ELSE IF 50 LE age_ref LE 64 THEN age_grp=3;
   ELSE IF age_ref GE 65 THEN age_grp=4;

IF 1959 LE ref_year LE 1961 THEN DO;    
  IF cutenure=1 THEN ownhome=1;
    ELSE IF cutenure=2 THEN ownhome=0;
END;

IF ref_year GE 1971 THEN DO;    
  IF cutenure IN(1,2,3) THEN ownhome=1;
    ELSE IF cutenure IN(4,5,6) THEN ownhome=0;
END;

            
* REGION IS MISSING FOR RURALS 1980-1995, SO CREATE REGION CODE;
            
IF bls_urbn=2 THEN region_code=5;
   ELSE IF bls_urbn=1 THEN region_code=region;
    
dem_group2=10*ftype+emp_type;
dem_group3=10*ftype+race_type;
dem_group4=10*ftype+region_code; 

dem_group6=100*ftype+10*ed_type+race1; 

age_ref_2=age_ref*age_ref;
age_ref_3=age_ref*age_ref*age_ref;


***************************************************************************
ASSETS AND DEBTS
***************************************************************************;

IF ckbkactx_="A" THEN ckbkactx=0;
IF secestx_="A" THEN secestx=0;
IF usbndx_="A" THEN usbndx=0;
IF savacctx_="A" THEN savacctx=0;
IF LIQUIDX_="A" THEN LIQUIDX=0;
IF STOCKX_="A" THEN STOCKX=0;

ARRAY ass_array1 ckbkactx secestx usbndx savacctx;
IF qyear LE 1131 THEN DO OVER ass_array1;
   IF rt1 NE 5 THEN ass_array1=.;
END;
ARRAY ass_array2 LIQUIDX STOCKX;
IF (1132 LE qyear LE 1145) OR srv_year=115 AND desflg='00' THEN DO OVER ass_array2;
	IF rt1 NE 5 THEN ass_array2=.;
END;
IF (srv_year GE 116) OR srv_year=115 AND desflg='10' THEN DO OVER ass_array2;
   IF rt1 NE 4 THEN ass_array2=.;
END;


IF 801 LE qyear LE 1131 THEN tot_assets=ckbkactx+ secestx+ usbndx+ savacctx;
	ELSE IF qyear GE 1132 THEN tot_assets=LIQUIDX+STOCKX;

	IF 1972 LE ref_year LE 1973 THEN tot_assets=assets72;
	* DON'T HAVE LEVELS IN 1960-61, ONLY CHANGES, ALSO NEED TO PUT IN 1961 $s BEFORE CONVERTING
	  TO 2005 $s SINCE REF_YEAR=1961 FOR ALL IN 60s;


r_tot_assets=tot_assets*cpi_u_rs_adj_17;
r_tot_debt=tot_debt2*cpi_u_rs_adj_17;   

r_house_val=all_prop_val*cpi_u_rs_adj_17; 
r_house_eq=(all_prop_val-principal-prin_hel-prin_loc)*cpi_u_rs_adj_17; 
r_house_debt=(principal+prin_hel+prin_loc)*cpi_u_rs_adj_17; 
r_tot_ass_incl_heq=r_tot_assets+r_house_eq;
r_tot_ass_incl_hval=r_tot_assets+r_house_val;
r_net_worth=r_tot_assets+r_house_eq-r_tot_debt;


* GEN PERIOD VAR FOR CONSUMPTION GROWTH BY ASSET QUINTILE RESULTS. ;
IF ref_year=1991 THEN period_grp=1;
   ELSE IF ref_year=2000 THEN period_grp=2;
   ELSE IF ref_year=2006 THEN period_grp=3;
   ELSE IF ref_year=2010 THEN period_grp=4;
   ELSE IF ref_year=2017 THEN period_grp=5;

***********************************************************************
THIS IS ADDED FOR CALCS OF WELL-MEASURED CONS IN STATA
***********************************************************************;
IF 59 LE srv_year LE 62 THEN gas_mo=gasoline_oil/4;
   ELSE IF 71 LE srv_year LE 74 THEN gas_mo=d_gas/4;
   ELSE IF srv_year GE 80 THEN gas_mo=SUM(OF gasmopq gasmocq);


RUN;

PROC MEANS;
RUN;

***********************************************************************
MERGE IN UTILITIES DATA
***********************************************************************;
PROC SORT DATA=poverty;
  BY qyear newid;
RUN;
PROC SORT DATA=alldat.utilities80_17;
  BY qyear newid; 
RUN;

DATA all_cons_data;
   MERGE poverty(IN=in1) alldat.utilities80_17;
   BY qyear newid; 
   IF in1;
RUN;

PROC MEANS;
RUN;
PROC SORT;
  BY srv_year; 
RUN;
PROC MEANS;
  BY srv_year; 
RUN;


proc export data=all_cons_data 
	OUTFILE="~/jim/data/data_for_others/all_cons_data_for_stata_80_17.dta"
	REPLACE;
run;


******************************************************************************
OUTPUT TO STATA FOR MELLY DECOMPOSITION WAS CREATED IN STATA (JH)
******************************************************************************;

ENDSAS;



